/**
 * TakenCourseBriefDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.TakenCourseBriefDTO;
import pits.util.Constants;
import pits.util.Logger;

public class TakenCourseBriefDAO {

	public final List<TakenCourseBriefDTO> getData(String employeeID) {
		List<TakenCourseBriefDTO> result = new ArrayList<TakenCourseBriefDTO>();
		TakenCourseBriefDTO runnerDTO = null;

		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement sqlStatement = null;

		ResultSet courseexpResultSet = null;
		ResultSet courseResultSet = null;
		ResultSet departResultSet = null;
		ResultSet langResultSet = null;
		ResultSet DBResultSet = null;
		ResultSet frameResultSet = null;
		ResultSet techResultSet = null;

		try {
			query = "SELECT * FROM courseexp WHERE EmpID=" + employeeID + " AND isDeleted=0";
			// new Logger().write(query);

			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);
			courseexpResultSet = sqlStatement.executeQuery();

			while (courseexpResultSet.next()) {

				query = "SELECT * FROM course WHERE CourseID=" + courseexpResultSet.getInt("CourseID")
				        + " AND isDeleted=0";
				// new Logger().write(query);
				sqlStatement = conn.prepareStatement(query);
				courseResultSet = sqlStatement.executeQuery();

				if (courseResultSet.next()) {

					runnerDTO = new TakenCourseBriefDTO();

					query = "SELECT * FROM department WHERE DepartID=" + courseResultSet.getInt("DepartID")
					        + " AND isDeleted=0";
					// new Logger().write(query);
					sqlStatement = conn.prepareStatement(query);
					departResultSet = sqlStatement.executeQuery();
					departResultSet.next();

					query = "SELECT * FROM language l, course_language cl" + " WHERE cl.CourseID="
					        + courseResultSet.getInt("CourseID") + " AND l.LangID=cl.LangID" + " AND l.isDeleted=0";
					// new Logger().write(query);
					sqlStatement = conn.prepareStatement(query);
					langResultSet = sqlStatement.executeQuery();
					String languages = "-";
					while (langResultSet.next()) {
						languages += ", " + langResultSet.getString("Name");
					}
					if (!languages.equals("-")) {
						languages = languages.substring(2);
					}

					query = "SELECT Name FROM pits_db.database d, course_database cd" + " WHERE cd.CourseID="
					        + courseResultSet.getInt("CourseID") + " AND d.DatabaseID=cd.DatabaseID"
					        + " AND d.isDeleted=0";
					// new Logger().write(query);
					sqlStatement = conn.prepareStatement(query);
					DBResultSet = sqlStatement.executeQuery();
					String DBMS = "-";
					while (DBResultSet.next()) {
						DBMS += ", " + DBResultSet.getString("Name");
					}
					if (!DBMS.equals("-")) {
						DBMS = DBMS.substring(2);
					}

					query = "SELECT * FROM framework f, course_framework cf" + " WHERE cf.CourseID="
					        + courseResultSet.getInt("CourseID") + " AND f.FrameID=cf.FrameID" + " AND f.isDeleted=0";
					// new Logger().write(query);
					sqlStatement = conn.prepareStatement(query);
					frameResultSet = sqlStatement.executeQuery();
					String frameTech = "-";
					while (frameResultSet.next()) {
						frameTech += ", " + frameResultSet.getString("Name");
					}

					query = "SELECT * FROM technology t, course_technology ct" + " WHERE ct.CourseID="
					        + courseResultSet.getInt("CourseID") + " AND t.TechID=ct.TechID" + " AND t.isDeleted=0";
					// new Logger().write(query);
					sqlStatement = conn.prepareStatement(query);
					techResultSet = sqlStatement.executeQuery();
					while (techResultSet.next()) {
						frameTech += ", " + techResultSet.getString("Name");
					}
					if (!frameTech.equals("-")) {
						frameTech = frameTech.substring(2);
					}

					runnerDTO.setName(courseResultSet.getString("Name"));
					runnerDTO.setDepartName(departResultSet.getString("DepartName"));
					runnerDTO.setPeriod(courseResultSet.getString("Period"));
					runnerDTO.setDescription(courseResultSet.getString("Description"));
					runnerDTO.setLang(languages);
					runnerDTO.setFrameTech(frameTech);
					runnerDTO.setDBMS(DBMS);
					runnerDTO.setGrade(courseexpResultSet.getString("Grade"));
					runnerDTO.setDateIssused(courseResultSet.getString("DateIssused"));
					runnerDTO.setCourseID(courseResultSet.getInt("CourseID"));

					result.add(runnerDTO);
				}
			}
		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				// courseexpResultSet.close();
				// courseResultSet.close();
				// departResultSet.close();
				// langResultSet.close();
				// DBResultSet.close();
				// frameResultSet.close();
				// techResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		// new Logger().write("TakenCourseBriefDAO.getData: Done");
		return result;
	}
}
